package com.and9.mchen.mweather.cw.demo;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.LinkedList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.and9.mchen.mweather.cw.db.DBManager;
import com.and9.mchen.mweather.cw.db.JdbcUtils;
import com.and9.mchen.mweather.cw.entity.Local;
import com.and9.mchen.mweather.cw.utils.PropertyUtil;

public class PropertyTest {

	private  Connection conn;
	private  PreparedStatement ps;
	private  ResultSet rs;
	
	/**
	 * @param args
	 * @throws IOException 
	 * @throws SQLException 
	 */
	public static void main(String[] args) throws IOException, SQLException {
		
//		connection.setAutoCommit(false);
//		System.out.println(list.get(0).getName());
//		sql = "INSERT demo(name,nameEN,pinyin,type,parentId,localCode) VALUES(?,?,?,?,?,?)";
//		PreparedStatement stmt=connection.prepareStatement(sql);
//		for(int i=0;i<list.size();i++){
//			stmt.setString(1, list.get(i).getName());
//			stmt.setString(2, list.get(i).getNameEN());
//			stmt.setString(3, list.get(i).getPinyin());
//			stmt.setString(4, list.get(i).getType());
//			stmt.setLong(5, list.get(i).getParentId());
//			stmt.setString(6, list.get(i).getLocalCode());
//			stmt.addBatch();
//		}
//		stmt.executeBatch();
//		connection.commit();
		
//		
//		PropertyTest p=new PropertyTest();
//		List<Local> provinces=p.getProvinces();
//		
//		for(Local pro: provinces){
//			pro.setChildlList(p.getChildCityByProvince(pro));
//			System.out.println(pro.getChildlList().size());
//		}
		PropertyTest p=new PropertyTest();
		List<Local> provinces=p.getProvinces();
		provinces.remove(0);
//		setCascadeWithNum(provinces, 2);
		List<Local> cityList=new LinkedList<Local>();
		for(Local pro: provinces){
		//pro.setChildlList(p.getChildCityByProvince(pro));
			cityList.addAll(p.getChildCityByProvince(pro));
		//System.out.println(pro.getChildlList().size());
		}
		setCascadeWithNum(cityList, 3);
		
	}

	public static void setCascadeWithNum(List<Local> list, int state) throws SQLException{
		String sql="update local set grade=? where id=? ";
		QueryRunner qr=new QueryRunner();
		Object[][] params=new Object[list.size()][2];
		for(int i=0; i<list.size(); i++){
			params[i][0]=state+"";
			params[i][1]=list.get(i).getId();
		}
		qr.batch(DBManager.getConnection(),sql, params);
	}
	
	public List<Local> getProvinces(){
		
		String sql="SELECT * FROM local where parentId=1";
		List<Local> list=new LinkedList<Local>();
		try {
			conn=JdbcUtils.getConnection();
			ps= conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()){
				Local local=new Local();
				//System.out.println(rs.getLong("id"));
				local.setId(rs.getLong("id"));
				local.setName(rs.getString("name"));
				local.setNameEN(rs.getString("nameEN"));
				local.setPinyin(rs.getString("pinyin"));
				local.setType(rs.getString("type"));
			//	local.setParentId(rs.getLong("parentId"));
				local.setLocalCode(rs.getLong("localCode"));
				list.add(local);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtils.free(rs, ps, conn);
			return list;
		}
		
	}
	
	public List<Local> getChildCityByProvince(Local province){
		String sql="SELECT * FROM local where parentId=?";
		List<Local> list=new LinkedList<Local>();
		try {
			conn=JdbcUtils.getConnection();
			ps= conn.prepareStatement(sql);
			ps.setLong(1, province.getId());
			rs=ps.executeQuery();
			while(rs.next()){
				Local local=new Local();
				//System.out.println(rs.getLong("id"));
				local.setId(rs.getLong("id"));
				local.setName(rs.getString("name"));
				local.setNameEN(rs.getString("nameEN"));
				local.setPinyin(rs.getString("pinyin"));
				local.setType(rs.getString("type"));
				//local.setParentId(rs.getLong("parentId"));
				local.setLocalCode(rs.getLong("localCode"));
				list.add(local);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			JdbcUtils.free(rs, ps, conn);
			return list;
		}
	}
	
	
	public static List<Local> getLocalList() throws SQLException{
		String sql="SELECT * FROM local WHERE localCode!=0";
		QueryRunner qr=new QueryRunner();
		List<Local> list=qr.query(DBManager.getConnection(),sql, new BeanListHandler(Local.class));
		return list;
	}
	
	private static void saveListInNewTable(List<Local> list) throws SQLException{
		QueryRunner qr=new QueryRunner();
		String sql = "INSERT local_identity(id,localCode) VALUES(?,?)";
		Object[][] params=new Object[list.size()][2];
		for(int i=0;i<list.size();i++){
			params[i][0]=list.get(i).getId();
			params[i][1]=list.get(i).getLocalCode();
		}
		qr.batch(DBManager.getConnection(), sql, params);
	}
}
